package com.alog.yzd.bookmall.dao.book;

import com.alog.yzd.bookmall.domain.book.Book;
import com.alog.yzd.bookmall.domain.catagory.Catagory;
import com.alog.yzd.bookmall.pager.Expression;
import com.alog.yzd.bookmall.pager.PageBaen;
import com.alog.yzd.bookmall.pager.PageConstants;
import com.alog.yzd.bookmall.utils.SqlUtil;
import com.alog.yzd.bookmall.utils.jdbc.TxQueryRunner;
import com.alog.yzd.commons.CommonUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class BookDao {
    private QueryRunner qr = new TxQueryRunner();
    /**
     * 删除图书
     */
    public void delete(String bid) throws SQLException {
        String sql = "delete from t_book where bid = ?";
        qr.update(sql, new Object[]{bid});
    }

    /**
     * 编辑图书
     */
    public void edit(Book book) throws SQLException {
        String sql = "update t_book set bname = ?, author=?, price=?, currPrice=?, discount=?, press=?, publishtime=?, edition=?, pageNum=?, wordNum=?, printtime=?, booksize=?, paper=?, cid=? where bid=?";
        Object[] params = new Object[]{ book.getBname(), book.getAuthor(), book.getPrice(), book.getCurrPrice(),
                book.getDiscount(), book.getPress(), book.getPublishtime(), book.getEdition(), book.getPageNum(),
                book.getWordNum(), book.getPrinttime(), book.getBooksize(), book.getPaper(), book.getCatagory().getCid(),
                book.getBid()};
        qr.update(sql, params);
    }
    /**
     * 添加图书
     */
    public void add(Book book) throws SQLException {
        String sql = "insert into t_book (bid, bname, author, price, currPrice, discount, press, publishtime, edition, pageNum, wordNum, printtime, booksize, paper, cid, image_w, image_b) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        Object[] params = new Object[]{book.getBid(), book.getBname(), book.getAuthor(), book.getPrice(), book.getCurrPrice(),
                book.getDiscount(), book.getPress(), book.getPublishtime(), book.getEdition(), book.getPageNum(),
                book.getWordNum(), book.getPrinttime(), book.getBooksize(), book.getPaper(), book.getCatagory().getCid(),
                book.getImage_w(), book.getImage_b()};
        qr.insert(sql, new ScalarHandler<>(), params);
    }

    /**
     * 查询某个分类下图书的个数
     */
    public int findBookCountByCatagory(String cid) throws SQLException {
        String sql = "select count(*) from t_book where cid = ?";
        Number number = qr.query(sql, new ScalarHandler<>(), new Object[]{cid});
        return number == null ? 0 : number.intValue();
    }

    /**
     * 通过BookId查询
     * @param bid
     * @return
     * @throws SQLException
     */
    public Book findByBid(String bid) throws SQLException {
        String sql = "select * from t_book b , t_category c where b.cid = c.cid and b.bid = ?";
        Map<String, Object> map = qr.query(sql, new MapHandler(), new Object[]{bid});
        Book book = CommonUtils.toBean(map, Book.class);
        Catagory c = CommonUtils.toBean(map, Catagory.class);
        book.setCatagory(c);

        if(map.get("pid") != null){
            Catagory parent = new Catagory();
            parent.setCid((String)map.get("pid"));
            c.setParent(parent);
        }
        return book;
    }

    /**
     * 按分类查询
     * @param cid
     * @param pc
     * @return
     * @throws SQLException
     */
    public PageBaen<Book> findByCatagory(String cid, int pc) throws SQLException {
        List<Expression> expressionList = new ArrayList<>();
        expressionList.add(new Expression("cid", "=", cid));
        return findByCriteria(expressionList, pc);
    }

    /**
     * 按书名模糊查询
     * @param bname
     * @param pc
     * @return
     * @throws SQLException
     */
    public PageBaen<Book> findByBname(String bname, int pc) throws SQLException {
        List<Expression> expressionList = new ArrayList<>();
        expressionList.add(new Expression("bname", "like", "%" + bname + "%"));
        return findByCriteria(expressionList, pc);
    }

    /**
     * 按作者模糊查询
     * @param author
     * @param pc
     * @return
     * @throws SQLException
     */
    public PageBaen<Book> findByAuthor(String author, int pc) throws SQLException {
        List<Expression> expressionList = new ArrayList<>();
        expressionList.add(new Expression("author", "like", "%" + author + "%"));
        return findByCriteria(expressionList, pc);
    }

    /**
     * 按出版社模糊查询
     * @param press
     * @param pc
     * @return
     * @throws SQLException
     */
    public PageBaen<Book> findByPress(String press, int pc) throws SQLException {
        List<Expression> expressionList = new ArrayList<>();
        expressionList.add(new Expression("press", "like", "%" + press + "%"));
        return findByCriteria(expressionList, pc);
    }

    /**
     * 多条件组合查询
     * @param book
     * @param pc
     * @return
     * @throws SQLException
     */
    public PageBaen<Book> findByCombination(Book book, int pc) throws SQLException {
        List<Expression> expressionList = new ArrayList<>();
        expressionList.add(new Expression("bname", "like", "%" + book.getBname() + "%"));
        expressionList.add(new Expression("author", "like", "%" + book.getAuthor() + "%"));
        expressionList.add(new Expression("press", "like", "%" + book.getPress() + "%"));
        return findByCriteria(expressionList, pc);
    }

    /**
     * 通用的查询方法
     * @param exprList, 条件集合
     * @param pc 当前页码
     * @return
     */
    private PageBaen<Book> findByCriteria(List<Expression> exprList, int pc) throws SQLException {
        //得到ps
        //得到tr
        //得到BeanList
        //创建PageBaen

        int ps = PageConstants.BOOK_PAGE_SIZE;
        List<Object> params = new ArrayList<>();
        String whereSql = SqlUtil.createWhereSql(exprList, params);

        String sql = "select count(*) from t_book " + whereSql;
        Number number = qr.query(sql, new ScalarHandler<>(), params.toArray());
        int tr = number.intValue();

        sql = "select * from t_book " + whereSql + " order by orderBy limit ?, ?";
        params.add((pc - 1) * ps);
        params.add(ps);
        List<Book> bookList = qr.query(sql, new BeanListHandler<Book>(Book.class), params.toArray());

        PageBaen<Book> pb = new PageBaen<>();
        pb.setBeanList(bookList);
        pb.setPc(pc);
        pb.setPs(ps);
        pb.setTr(tr);
        return pb;
    }


}
